﻿using DatabaseProxy.Attributes;
using DatabaseProxy.ObjectSql;
using Microsoft.Data.SqlClient;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;

namespace DatabaseProxy.DatabaseProxy;
public partial class DatabaseProxyCore
{
    /// <summary>
    /// 通过主键获取实体对象
    /// </summary>
    /// <typeparam name="T">对象类型</typeparam>
    /// <param name="id">主键</param>
    /// <returns></returns>
    public T? GetEntityById<T>(int id) where T : class, IDatabaseTable
    {
        {
            // 创建数据库连接对象并打开
            using SqlConnection conn = new(ConnectionString);
            conn.Open();

            // 创建数据库命令对象并执行查询
            using SqlCommand command = conn.CreateCommand();
            Type type = typeof(T);
            var comment = Activator.CreateInstance(type) as T;
            string sql = ObjectSqlManager<T>.QueryByPrimaryKeySql;
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@id", id));

            // 获取查询数据并返回
            using SqlDataReader reader = command.ExecuteReader();
            if (reader.HasRows)
            {
                if (reader.Read())
                {
                    foreach (var pair in Tool<T>.Columns)
                    {
                        pair.Value.SetValue(comment, reader[pair.Key] is DBNull ? null : reader[pair.Key]);
                    }
                }
            }
            return comment;
        }
    }
    /// <summary>
    /// 获取所有数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <returns></returns>
    public List<T> GetEntities<T>(Expression<Func<T, bool>>? expression = null, PropertyInfo? orderExpression = null, string direction = "ASC", int page = 0, int size = 0) where T : IDatabaseTable
    {
        // 返回的实体对象
        List<T> comment = new();

        // 创建数据库连接对象并打开
        using SqlConnection conn = new(ConnectionString);
        conn.Open();

        // 创建数据库命令对象并执行查询
        using SqlCommand command = conn.CreateCommand();
        Type type = typeof(T);
        string sql = ObjectSqlManager<T>.QuerySql;
        if (expression != null)
        {
            SqlVisitor visitor = new();
            visitor.Visit(expression);
            var condition = visitor.GetSql();
            sql += condition;
        }
        if (orderExpression != null || (page > 0 && size > 0))
        {
            SqlPageVisitor<T> pageVisitor = new();
            pageVisitor.OrderAndPage(orderExpression, direction, page, size);
            sql += pageVisitor.GetSql();
        }
        command.CommandText = sql;

        // 获取查询数据并返回
        using SqlDataReader reader = command.ExecuteReader();
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                T? t = (T?)Activator.CreateInstance(type);
                if (t != null)
                {
                    foreach (var pair in Tool<T>.Columns)
                    {
                        pair.Value.SetValue(t, reader[pair.Key] is DBNull ? null : reader[pair.Key]);
                    }
                    comment.Add(t);
                }
            }
        }
        return comment;
    }
    /// <summary>
    /// 新增单个数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity"></param>
    /// <returns></returns>
    public int InsertEntity<T>(T entity) where T : IDatabaseTable
    {
        var sql = ObjectSqlInserter<T>.Sql;
        int result = ExecuteSql(sql, command => command.ExecuteNonQuery());
        return result;
    }
    /// <summary>
    /// 新增单个数据，带有事务
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity"></param>
    /// <returns></returns>
    public int InsertEntityInTransaction<T>(T entity) where T : IDatabaseTable
    {
        using SqlConnection conn = new(ConnectionString);
        conn.Open();
        var sql = ObjectSqlInserter<T>.Sql;
        using SqlCommand command = new(sql, conn);
        command.Parameters.AddRange(
            Tool<T>.Columns.Values
            .Where(l => !l.IsDefined(typeof(IdentityAttribute), false))
            .Select(l => new SqlParameter(l.Name, l.GetValue(entity) ?? DBNull.Value)).ToArray()
        );
        var result = 0;
        using SqlTransaction transaction = conn.BeginTransaction();
        try
        {
            result = command.ExecuteNonQuery();
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
        }
        return result;
    }
    /// <summary>
    /// 批量新增数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entities"></param>
    /// <returns></returns>
    public int InsertEntities<T>(IEnumerable<T> entities) where T : IDatabaseTable
    {
        if (entities == null || entities.Count() == 0) return 0;
        int result = 0;
        string sql = ObjectSqlInserter<T>.MultipleSql(entities.Count());
        using SqlConnection conn = new(ConnectionString);
        conn.Open();
        using SqlCommand command = new(sql, conn);
        int i = 0;
        var propertis = typeof(T).GetProperties().Where(l => !l.IsDefined(typeof(IdentityAttribute), false));
        foreach (var entity in entities)
        {
            command.Parameters.AddRange(
                propertis.Select(l =>
                {
                    var value = l.GetValue(entity);
                    if (l.PropertyType == typeof(string) && l.IsDefined(typeof(StringLengthAttribute), false))
                    {
                        var stringLengthAttribute = l.GetCustomAttribute<StringLengthAttribute>();
                        var valuestring = value?.ToString();
                        if (stringLengthAttribute != null && !string.IsNullOrEmpty(valuestring) && valuestring.Length > stringLengthAttribute.Length)
                        {
                            value = valuestring.Substring(0, stringLengthAttribute.Length);
                        }
                    }
                    return new SqlParameter(l.Name + i, value ?? DBNull.Value);
                }).ToArray()
            );
            i++;
        }
        result = command.ExecuteNonQuery();
        return result;
    }
    /// <summary>
    /// 批量新增数据，带有事务
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entities"></param>
    /// <returns></returns>
    public int InsertEntitiesWithTransaction<T>(IEnumerable<T> entities) where T : IDatabaseTable
    {
        if (entities == null || entities.Count() == 0) return 0;
        int result = 0;
        string sql = ObjectSqlInserter<T>.MultipleSql(entities.Count());
        using SqlConnection conn = new(ConnectionString);
        conn.Open();
        using SqlCommand command = new(sql, conn);
        int i = 0;
        var propertis = Tool<T>.Columns.Where(l => !l.Value.IsDefined(typeof(IdentityAttribute), false));
        foreach (var entity in entities)
        {
            command.Parameters.AddRange(
                propertis.Select(l =>
                {
                    var value = l.Value.GetValue(entity);
                    if (l.Value.PropertyType == typeof(string) && l.Value.IsDefined(typeof(StringLengthAttribute), false))
                    {
                        var stringLengthAttribute = l.Value.GetCustomAttribute<StringLengthAttribute>();
                        var valuestring = value?.ToString();
                        if (stringLengthAttribute != null && !string.IsNullOrEmpty(valuestring) && valuestring.Length > stringLengthAttribute.Length)
                        {
                            value = valuestring[..stringLengthAttribute.Length];
                        }
                    }
                    return new SqlParameter(l.Value.Name + i, value ?? DBNull.Value);
                }).ToArray()
            );
            i++;
        }
        using SqlTransaction transaction = conn.BeginTransaction();
        try
        {
            result = command.ExecuteNonQuery();
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
        }
        return result;
    }
    /// <summary>
    /// 修改数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entity"></param>
    /// <returns></returns>
    public int UpdateEntity<T>(T entity) where T : IDatabaseTable
    {
        var result = 0;
        using SqlConnection conn = new(ConnectionString);
        conn.Open();
        string sql = ObjectSqlUpdater<T>.Sql;
        using SqlCommand command = new(sql.ToString(), conn);
        var primaryProperty = ObjectSqlUpdater<T>.PrimaryKeyProperty;
        command.Parameters.Add(new SqlParameter
        {
            ParameterName = primaryProperty.Name,
            Value = primaryProperty.GetValue(entity) ?? DBNull.Value
        });
        command.Parameters.AddRange(typeof(T).GetProperties().Where(l => !l.IsDefined(typeof(PrimaryKeyAttribute), false)).Select(l =>
        {
            var value = l.GetValue(entity) ?? DBNull.Value;
            if (l.PropertyType == typeof(string) && value != DBNull.Value && l.IsDefined(typeof(StringLengthAttribute), false))
            {
                var stringLengthAttribute = l.GetCustomAttribute<StringLengthAttribute>();
                var valuestring = value?.ToString();
                if (stringLengthAttribute != null && !string.IsNullOrEmpty(valuestring) && valuestring.Length > stringLengthAttribute.Length)
                {
                    value = valuestring.Substring(0, stringLengthAttribute.Length);
                }
            }
            SqlParameter sqlParameter = new()
            {
                ParameterName = l.Name,
                Value = value
            };
            return sqlParameter;
        }).ToArray());
        result = command.ExecuteNonQuery();
        return result;
    }
    /// <summary>
    /// 批量修改数据
    /// </summary>
    /// <typeparam name="T"></typeparam>
    /// <param name="entities"></param>
    /// <returns></returns>
    public int UpdateEntities<T>(IEnumerable<T> entities) where T : IDatabaseTable
    {
        var result = 0;
        using SqlConnection conn = new(ConnectionString);
        conn.Open();
        string sql = ObjectSqlUpdater<T>.MultipleSql(entities.Count());
        using SqlCommand command = new(sql, conn);
        List<SqlParameter> parameters = new();
        for (int i = 0; i < entities.Count(); i++)
        {
            foreach (var pair in Tool<T>.Columns)
            {
                parameters.Add(new SqlParameter
                {
                    ParameterName = pair.Value.Name + i,
                    Value = pair.Value.GetValue(entities.ElementAt(i)) ?? DBNull.Value
                });
            }
        }
        command.Parameters.AddRange([.. parameters]);
        result = command.ExecuteNonQuery();
        return result;
    }
}
